﻿using System;
using System.Collections.Generic;
using OrmSmackdown.LLBLGen.EntityClasses;
using System.Linq;
using System.Text;
using OrmSmackdown.LLBLGen.Linq;

namespace OrmSmackdown.LLBLGen
{
    public class Exerciser : IExerciser
    {
        private LinqMetaData ctx;
        public Exerciser()
        {
            ctx = new LinqMetaData();
        }

        public void Insert()
        {
            var company1 = new EntityClasses.CompaniesEntity() { CompanyName = Constants.Company1 };
            var company2 = new EntityClasses.CompaniesEntity() { CompanyName = Constants.Company2 };

            var managerRole = new EntityClasses.RolesEntity { RoleName = Constants.ManagerRole, IsForContact = false, IsForPortal = true };
            var employeeRole = new EntityClasses.RolesEntity { RoleName = Constants.EmployeeRole, IsForContact = false, IsForPortal = true };
            var contactRole = new EntityClasses.RolesEntity { RoleName = Constants.ContactRole, IsForContact = true, IsForPortal = false };

            var user1 = new UserEntity { FirstName = Constants.User1FirstName, LastName = Constants.User1LastName, Email = Constants.User1Email, MyRole = managerRole };
            var user2 = new UserEntity { FirstName = Constants.User2FirstName, LastName = Constants.User2LastName, Email = Constants.User2Email, MyRole = employeeRole };
            var user3 = new UserEntity { FirstName = Constants.User3FirstName, LastName = Constants.User3LastName, Email = Constants.User3Email, MyRole = employeeRole };
            var user4 = new UserEntity { FirstName = Constants.User4FirstName, LastName = Constants.User4LastName, Email = Constants.User4Email, MyRole = managerRole };
            var user5 = new UserEntity { FirstName = Constants.User5FirstName, LastName = Constants.User5LastName, Email = Constants.User5Email, MyRole = employeeRole };

            var contact1 = new ContactEntity { FirstName = Constants.Contact1FirstName, LastName = Constants.Contact1LastName, Email = Constants.Contact1Email, MyRole = contactRole, Company = company1 };
            var contact2 = new ContactEntity { FirstName = Constants.Contact2FirstName, LastName = Constants.Contact2LastName, Email = Constants.Contact2Email, MyRole = contactRole, Company = company1 };
            var contact3 = new ContactEntity { FirstName = Constants.Contact3FirstName, LastName = Constants.Contact3LastName, Email = Constants.Contact3Email, MyRole = contactRole, Company = company2 };
            var contact4 = new ContactEntity { FirstName = Constants.Contact4FirstName, LastName = Constants.Contact4LastName, Email = Constants.Contact4Email, MyRole = contactRole, Company = company2 };
            var contact5 = new ContactEntity { FirstName = Constants.Contact5FirstName, LastName = Constants.Contact5LastName, Email = Constants.Contact5Email, MyRole = contactRole, Company = company2 };
            var contact6 = new ContactEntity { FirstName = Constants.Contact6FirstName, LastName = Constants.Contact6LastName, Email = Constants.Contact6Email, MyRole = contactRole, Company = company2 };

            var portal1 = new PortalsEntity { PortalName = Constants.Portal1, AdminUser = user1 };
            var portal2 = new PortalsEntity { PortalName = Constants.Portal2, AdminUser = user4 };

            company1.Save();
            company2.Save();

            managerRole.Save();
            employeeRole.Save();
            contactRole.Save();

            user1.Save();
            user2.Save();
            user3.Save();
            user4.Save();
            user5.Save();

            contact1.Save();
            contact2.Save();
            contact3.Save();
            contact4.Save();
            contact5.Save();
            contact6.Save();

            portal1.Save(true);
            portal2.Save(true);

            ///?Not sure why these are saving?
            //portal1.PortalUsers.Add(user1);

            //portal1.PortalUsers.Add(user2);
            //portal2.PortalUsers.Add(user2);

            //portal1.PortalUsers.Add(user3);
            //portal2.PortalUsers.Add(user3);

            //portal2.PortalUsers.Add(user4);

            //portal2.PortalUsers.Add(user5);

            var ppx11 = new PortalPeopleXrefEntity() { Portal = portal1, Person = user1 };

            var ppx21 = new PortalPeopleXrefEntity() { Portal = portal1, Person = user2 };
            var ppx22 = new PortalPeopleXrefEntity() { Portal = portal2, Person = user2 };

            var ppx31 = new PortalPeopleXrefEntity() { Portal = portal1, Person = user3 };
            var ppx32 = new PortalPeopleXrefEntity() { Portal = portal2, Person = user3 };

            var ppx42 = new PortalPeopleXrefEntity() { Portal = portal2, Person = user4 };

            var ppx52 = new PortalPeopleXrefEntity() { Portal = portal2, Person = user5 };

            ppx11.Save();
            ppx21.Save();
            ppx22.Save();
            ppx31.Save();
            ppx32.Save();
            ppx42.Save();
            ppx52.Save();


            portal1.PortalUsers.SaveMulti();
            portal2.PortalUsers.SaveMulti();

            AddAddress( user1, "1000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( user2, "2000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( user3, "3000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( user4, "4000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( user5, "5000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( user1, "1100 Some St.", string.Empty, Constants.TownWeCareAbout, "ST", "12345", "1234", true);
            AddAddress( user2, "1200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( user3, "1300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( user4, "1400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( user5, "1500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);

            AddAddress( contact1, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( contact2, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( contact3, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( contact4, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( contact5, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( contact6, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress( contact1, "8000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( contact2, "8100 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( contact3, "8200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( contact4, "8300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( contact5, "8400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress( contact6, "8500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
        }

        private void AddAddress(PeopleEntity p, string line1, string line2, string city, string state, string zip, string zip4, bool isDefault)
        {
            var addr = new AddressesEntity { Line1 = line1, Line2 = line2, City = city, State = state, ZipCode = zip, ZipCodePlusFour = zip4, IsDefault = isDefault, Person = p };
            addr.Save();
        }


        public IList<string> GetUserEmails()
        {           
            return (from user in ctx.User select user.Email).ToList();

        }

        public IList<string> GetContactEmails()
        {
            return (from user in ctx.Contact select user.Email).ToList();
        }

        public IList<string> GetPortalAdminEmails()
        {
            return (from portals in ctx.Portals select portals.AdminUser.Email).ToList();
        }

        public IList<string> GetContactEmailsForCompany(string companyName)
        {
            return ctx.Companies.Where(cmp => cmp.CompanyName == companyName).FirstOrDefault().People.Select(fld => fld.Email).ToList();
        }

        public int GetNumberOfPortals()
        {
            return ctx.Portals.Count();
        }

        public int GetNumberOfUsers()
        {
            return ctx.User.Count();
        }

        public int GetNumberOfContacts()
        {
            return ctx.Contact.Count();
        }

        public string GetDefaultCity(string email)
        {
            // Join seems to generate invalid SQL
            //var city = from address in ctx.Addresses
            //           join usr in ctx.People on address.Person.PersonId equals usr.PersonId
            //           where address.IsDefault == true && usr.Email == email
            //           select address.City;

            //return city.FirstOrDefault();

            var user = ctx.People.Where(p => p.Email == email).FirstOrDefault();

            return user.Addresses.Where(a => a.IsDefault == true).FirstOrDefault().City;
        }

        public string GetAdminForPortal(string portalName)
        {
            // Join seems to generate invalid SQL
            //var email = from prsn in ctx.User
            //            join prt in ctx.Portals on prsn equals prt.AdminUser
            //            where prt.PortalName == portalName
            //            select prsn.Email;

            //return email.FirstOrDefault();

            var portal = ctx.Portals.Where(prt => prt.PortalName == portalName).FirstOrDefault();

            return portal.AdminUser.Email;

        }

        public double GetAverageUsersPerPortal()
        {
            //Get error message "Count" isn't mapped to database construct.
            //return ctx.Portals.Average(prt => prt.PortalUsers.Count);

            var portals = ctx.Portals.ToList();

            ///Ehhh... on this one, fairly certain there is a better way, runs three
            ///queries, yuk!
            int numbUsers = 0;
            foreach (var portal in portals)
                numbUsers += ((PortalsEntity)portal).PortalUsers.Count;

            return (double)numbUsers / (double)portals.Count;
        }


        public void Dispose()
        {
            
        }
    }
}
